﻿
using HCApp.UI.Windows.Views;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Input;
using System.Linq;
using System.Collections.Generic;
using Syncfusion.Windows.Tools.Controls;
using MainAppEntryPrismWpf.Utilities;
using System.Data;
using System.Reflection;
using System.IO;
using System.ComponentModel;
using NPOI.SS.Formula.Functions;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using NPOI.XSSF.UserModel;
using SCDB.ViewModels;
using System;

namespace MainAppEntryPrismWpf.Views
{
    /// <summary>
    /// Interaction logic for YGXXManagePage
    /// </summary>
    public partial class YGXXManagePage : ContentPageBase
    {       
        public DataClassesSCDBDataContext SCDBDataContent;

        private int MaxBH = -1;

        private DSZX_YGXXB SelectedYG =new DSZX_YGXXB();

        private List<DSZX_YGXXB> ygxxList;    //所有列表  同数据库中数据同

        public YGXXManagePage()
        {
            InitializeComponent();

            SCDBDataContent = new DataClassesSCDBDataContext();

            // datagrid.ItemsSource = (this.datagrid.DataContext as SCDB.ViewModels.UserInfoViewModel).Users;
          
            datagrid.ItemsSource = (from data in SCDBDataContent.DSZX_YGXXB
                                    where data.CJH== "单丝"
                                    select data).ToList();
        }       

        private void CheckBox_PreviewMouseLeftButtonDown(object sender, MouseButtonEventArgs e)
        {
            e.Handled = true;
            var checkBox = sender as CheckBox;
            if (checkBox != null)
            {
                checkBox.IsChecked = !checkBox.IsChecked;
            }

        }


        private void ClearControls()
        {
            this.ygbhTB.Text = "";
            this.ygxmTB.Text = "";

            this.xbSelect.SelectedIndex = 0;
            this.gzSelect.SelectedIndex = 0;
            this.jsdjSelect.SelectedIndex = 0;
            this.cjSelect.SelectedIndex = 0;


        }

        private void UpdateControls()
        {
            if (SelectedYG!=null)
            {
                this.ygbhTB.Text = SelectedYG.YGBH.ToString();
                this.ygxmTB.Text = SelectedYG.YGXM.ToString();
                this.xbSelect.SelectedIndex = SelectedYG.YGXB.ToString() == "男" ? 0 : 1;
                this.gzSelect.SelectedIndex = gzToIndex(SelectedYG.GZ.ToString());
                this.jsdjSelect.SelectedIndex = jsdjToIndex((int)SelectedYG.JSDJ);
                this.cjSelect.SelectedIndex = SelectedYG.CJH.ToString() == "单丝" ? 0 : 1;
            }          

        }

        private int gzToIndex(string gzString)
        {
            int kindIndex = -1;
            switch (gzString)
            {
                case "工种1":
                    kindIndex = 0;
                    break;
                case "工种2":
                    kindIndex = 1;
                    break;
                case "工种3":
                    kindIndex = 2;
                    break;
                default:
                    break;
            }
            return kindIndex;

        }

        private int jsdjToIndex(int jsdj)

        { 
            return jsdj - 1;  
           

        }


        private int GexMaxBH()
        {
           var MaxBHBody= SCDBDataContent.DSZX_YGXXB.OrderByDescending(x => x.YGBH).FirstOrDefault();
            if (MaxBHBody==null)
            {
                MaxBH = 2001;
                return -1;

            }
            else
            {
                MaxBH =(int) MaxBHBody.YGBH;
                return 1;
            }
           
        }


        private void inquiry_Click(object sender, RoutedEventArgs e)
        {

            ygxxList =SCDBDataContent.DSZX_YGXXB.Where(i => i.YGXM.Contains(this.ygxmTB.Text.ToString())).ToList();
            datagrid.ItemsSource = ygxxList;

        }

        private void add_Click(object sender, RoutedEventArgs e)
        {

            
            var fdfCJH = (this.cjSelect.SelectedItem as ComboBoxItem).Content.ToString();

            GexMaxBH();

            var currentYG = new DSZX_YGXXB() {
                // YGBH = MaxBH + 1,
                YGBH = Convert.ToInt32(this.ygbhTB.Text.Trim()),
                YGXM = this.ygxmTB.Text,
                CJH = (this.cjSelect.SelectedItem as ComboBoxItem).Content.ToString(),
                JSDJ = int.Parse((this.jsdjSelect.SelectedItem as  ComboBoxItem).Content.ToString()),
                GZ= (this.gzSelect.SelectedItem as ComboBoxItem).Content.ToString(),
                YGXB= (this.xbSelect.SelectedItem as ComboBoxItem).Content.ToString(),
            };          

            SCDBDataContent.DSZX_YGXXB.InsertOnSubmit(currentYG);
            SCDBDataContent.SubmitChanges();

            ygxxList= (from data 
                      in SCDBDataContent.DSZX_YGXXB
                       where data.CJH == "单丝"
                       select data).ToList();
            datagrid.ItemsSource = ygxxList;
        }

        private void modify_Click(object sender, RoutedEventArgs e)
        {
            if (SelectedYG!= null)
            {
                var toModifyObject = SCDBDataContent.DSZX_YGXXB.Where(a => a.ID == SelectedYG.ID).FirstOrDefault();

                if (toModifyObject!=null)
                {
                    toModifyObject.YGBH = Convert.ToInt32(this.ygbhTB.Text.Trim());
                    toModifyObject.YGXB = this.ygxmTB.Text.ToString();
                    toModifyObject.CJH = (this.cjSelect.SelectedItem as ComboBoxItem).Content.ToString();
                    toModifyObject.JSDJ = int.Parse((this.jsdjSelect.SelectedItem as ComboBoxItem).Content.ToString());
                    toModifyObject.GZ = (this.gzSelect.SelectedItem as ComboBoxItem).Content.ToString();
                    toModifyObject.YGXB = (this.xbSelect.SelectedItem as ComboBoxItem).Content.ToString();

                    SCDBDataContent.SubmitChanges();

                    ygxxList = (from data
                              in SCDBDataContent.DSZX_YGXXB
                              where data.CJH == "单丝"
                                select data).ToList();
                    datagrid.ItemsSource = ygxxList;

                }
                else
                {
                    MessageBox.Show("没有找到！！！");
                }
            }    

          


        
        }

        private void delete_Click(object sender, RoutedEventArgs e)
        {
            if (SelectedYG != null)
            {
                var toModifyObject = SCDBDataContent.DSZX_YGXXB.Where(a => a.ID == SelectedYG.ID).FirstOrDefault();

                if (toModifyObject != null)
                {
                    SCDBDataContent.DSZX_YGXXB.DeleteOnSubmit(toModifyObject);

                    SCDBDataContent.SubmitChanges();

                    ygxxList = (from data
                              in SCDBDataContent.DSZX_YGXXB
                              where data.CJH== "单丝"
                                select data).ToList();
                    datagrid.ItemsSource = ygxxList;
                }
                else
                {
                    MessageBox.Show("没有找到！！！");
                }
            }                                                                  

        }

        private void datagrid_SelectionChanged(object sender, Syncfusion.UI.Xaml.Grid.GridSelectionChangedEventArgs e)
        {

            SelectedYG = this.datagrid.SelectedItem as DSZX_YGXXB;

            if (SelectedYG !=null)
            {
                UpdateControls();
            }
            else
            {
                ClearControls();
            }
           //更新控件信息，便于修改用户信息
                      
        }

        //public static DataTable CopyToDataTable<T>(this IEnumerable<T> array)
        //{
        //    var ret = new DataTable();
        //    foreach (PropertyDescriptor dp in TypeDescriptor.GetProperties(typeof(T)))
        //        ret.Columns.Add(dp.Name, dp.PropertyType);
        //    foreach (T item in array)
        //    {
        //        var Row = ret.NewRow();
        //        foreach (PropertyDescriptor dp in TypeDescriptor.GetProperties(typeof(T)))
        //            Row[dp.Name] = dp.GetValue(item);
        //        ret.Rows.Add(Row);
        //    }
        //    return ret;
        //}

        private void export_Click(object sender, RoutedEventArgs e)
        {
            string dllFilePath = Assembly.GetExecutingAssembly().Location;
            string dllDirectoryPath = Path.GetDirectoryName(dllFilePath);

            string excelNameABC = Path.Combine(dllDirectoryPath, "数据导出格式.xlsx");
            DataTable questionsABC = null;
            DataRow zone1 = null;
            //ygxxList = (from data in SCDBDataContent.DSZX_YGXXB
            //            select data).ToList();

            //   DataTable dt = TableListConverter.ListToTable(ygxxList);

            //DataTable dt = ygxxList.ToDataTable();

            //using (ExcelHelper excelHelper = new ExcelHelper(excelNameABC))
            //{
            ////    excelHelper.DataTableToExcel(dt, "MySheet", false);                
            //}


            //ExportProductionDaily(dt,excelNameABC);


            var filePath = $"d:\\";
            var fileName = $"{DateTime.Now.ToString("yyyyMMddHHmmss")}.xlsx";
            var header = new List<ExcelHeader>() {
        new ExcelHeader (){ FirstCol=0, LastCol=4, FirstRow=0, LastRow=0, Value="单丝生产日工资表" },//(第一行)(跨列0-6)
        new ExcelHeader (){ FirstCol=4, LastCol=4, FirstRow=1, LastRow=1, Value="日期：" },//(第二行)(跨列0-6)
        
        new ExcelHeader (){ FirstCol=0, LastCol=0, FirstRow=2, LastRow=2, Value="工号" },//第一列，跨两行(2-3)
		new ExcelHeader (){ FirstCol=1, LastCol=1, FirstRow=2, LastRow=2, Value="姓名" },//跨两列(1,2),同一行(第三行)
		new ExcelHeader (){ FirstCol=2, LastCol=2, FirstRow=2, LastRow=2, Value="规格" },
        new ExcelHeader (){ FirstCol=3, LastCol=3, FirstRow=2, LastRow=2, Value="产量" },
        new ExcelHeader (){ FirstCol=4, LastCol=4, FirstRow=2, LastRow=2, Value="工资" },

    };

            var listData2 = new List<DansiDailySalary>() {
        new DansiDailySalary (){ YGBH="2009", YGXM="张三", CPGG= "g456",SCCL=150, YGGZ=600.8, },
        new DansiDailySalary (){ YGBH="2012", YGXM="liu23", CPGG= "g456",SCCL=350, YGGZ=1200.8, },
    };
            var data = new ExcelData<DansiDailySalary> { StartCol = 0, StartRow = 3, Data = listData2 };
            ExcelHelper.ExportExcel(filePath, fileName, header, data);





        }

        public void Export(DataTable dtSource, string strFileName, DataView dvXH = null)
        {
            //创建工作簿 office2007以上
            XSSFWorkbook workbook = new XSSFWorkbook();
            //为工作簿创建工作表并命名
            ISheet sheet = workbook.CreateSheet("商品表");

            ICellStyle dateStyle = workbook.CreateCellStyle();

            IDataFormat format = workbook.CreateDataFormat();
            dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");

            #region 表头及样式
            int cellIndex = 0;
            IRow headerRow = sheet.CreateRow(0);
            for (int i = 0; i < dtSource.Columns.Count; i++)
            {
                #region MyRegion
                string ColumnsName = dtSource.Columns[i].ToString();
                if (dtSource.Columns[i].ColumnName.EndsWith("XH"))
                {
                    ColumnsName = "序号";
                    sheet.SetColumnWidth(cellIndex, 3000);
                    //sheet.SetColumnHidden(cellIndex, true);隐藏指定列
                }
                else if (dtSource.Columns[i].ColumnName.EndsWith("GoogName"))
                {
                    ColumnsName = "商品名称";
                    sheet.SetColumnWidth(cellIndex, 10000);//设置列宽
                }
                else if (dtSource.Columns[i].ColumnName.EndsWith("Num"))
                {
                    ColumnsName = "数量";
                    sheet.SetColumnWidth(cellIndex, 5000);
                }

                else if (dtSource.Columns[i].ColumnName.EndsWith("Summation"))
                {
                    ColumnsName = "合计（元）";
                    sheet.SetColumnWidth(cellIndex, 5000);
                }
                #endregion

                //设置行高
                headerRow.HeightInPoints = 50;
                headerRow.CreateCell(cellIndex).SetCellValue(ColumnsName);

                ICellStyle headStyle = workbook.CreateCellStyle();
                headStyle.WrapText = true;
                IFont font = workbook.CreateFont();
                //字体大小
                font.FontHeightInPoints = 12;
                font.Boldweight = 360;
                headStyle.SetFont(font);
                headerRow.GetCell(cellIndex).CellStyle = headStyle;
                cellIndex++;
            }
            #endregion

            int rowIndex = 1;//行数一定要从1行开始
            int count = 1;
            int startRow = 1;
            //DataView dvSource = dtSource.DefaultView;
            //if (dvXH != null)
            //{
            //    foreach (DataRowView drv in dvXH)
            //    {//1-10.11-12,13-14,15-16
            //        int rowcout = 0;
            //        dvSource.RowFilter = "XH='" + drv["XH"] + "'";
            //        foreach (DataRowView row in dvSource)
            //        {
            //            #region 填充内容
            //            IRow dataRow = sheet.CreateRow(rowIndex);

            //            //序号
            //            ICell newCel0 = dataRow.CreateCell(0);
            //            ICellStyle style0 = workbook.CreateCellStyle();
            //            style0.DataFormat = format.GetFormat("text");
            //            newCel0.SetCellValue(row["XH"].ToString());

            //            //标的名称
            //            ICell newCel2 = dataRow.CreateCell(1);
            //            ICellStyle style2 = workbook.CreateCellStyle();
            //            style2.DataFormat = format.GetFormat("text");
            //            newCel2.SetCellValue(row["GoogName"].ToString());

            //            //标的数量
            //            ICell newCel4 = dataRow.CreateCell(2);
            //            ICellStyle style4 = workbook.CreateCellStyle();
            //            style4.DataFormat = format.GetFormat("text");
            //            newCel4.SetCellValue(row["Num"].ToString());

            //            //合计（元）
            //            ICell newCel8 = dataRow.CreateCell(3);
            //            ICellStyle style8 = workbook.CreateCellStyle();
            //            style8.DataFormat = format.GetFormat("text");
            //            newCel8.SetCellValue(row["Summation"].ToString());
            //            #endregion

            //            rowIndex++;
            //            rowcout++;
            //        }

            //        if (count == 1)
            //        {
            //            //合并行数
            //            sheet.AddMergedRegion(new CellRangeAddress(startRow, rowcout, 3, 3));
            //            startRow = startRow + rowcout;
            //        }
            //        else
            //        {
            //            sheet.AddMergedRegion(new CellRangeAddress(startRow, startRow + rowcout - 1, 3, 3));
            //            startRow = startRow + rowcout;
            //        }
            //        count++;
            //    }
            //}
            //else
            //{
            //    #region MyRegion
            //    foreach (DataRowView row in dvSource)
            //    {
            //        #region 填充内容
            //        IRow dataRow = sheet.CreateRow(rowIndex);

            //        //序号
            //        ICell newCel0 = dataRow.CreateCell(0);
            //        ICellStyle style0 = workbook.CreateCellStyle();
            //        style0.DataFormat = format.GetFormat("text");
            //        newCel0.SetCellValue(row["XH"].ToString());

            //        //商品名称
            //        ICell newCel1 = dataRow.CreateCell(1);
            //        ICellStyle style1 = workbook.CreateCellStyle();
            //        style1.DataFormat = format.GetFormat("text");
            //        newCel1.SetCellValue(row["GoogName"].ToString());

            //        //数量
            //        ICell newCel2 = dataRow.CreateCell(2);
            //        ICellStyle style2 = workbook.CreateCellStyle();
            //        style2.DataFormat = format.GetFormat("text");
            //        newCel2.SetCellValue(row["Num"].ToString());

            //        //合计（元）
            //        ICell newCel3 = dataRow.CreateCell(3);
            //        ICellStyle style3 = workbook.CreateCellStyle();
            //        style3.DataFormat = format.GetFormat("text");
            //        newCel3.SetCellValue(row["Summation"].ToString());
            //        #endregion
            //        rowIndex++;
            //    }
            //    #endregion
            //}
            #region 拼接最后一行
            IFont fontLast = workbook.CreateFont();
            fontLast.FontHeightInPoints = 30;
            fontLast.Boldweight = 480;
            IRow dataRowLast = sheet.CreateRow(rowIndex);
            dataRowLast.HeightInPoints = 40;
            ICell newCelLast = dataRowLast.CreateCell(0);
            ICellStyle styleLast = workbook.CreateCellStyle();
            styleLast.DataFormat = format.GetFormat("text");
            styleLast.SetFont(fontLast);
            newCelLast.SetCellValue("制作人：张三");
            sheet.AddMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 0, 3));
            #endregion

            MemoryStream stream = new MemoryStream();
            workbook.Write(stream);
            var buf = stream.ToArray();
            using (FileStream fs = new FileStream(strFileName, FileMode.Create, FileAccess.Write))
            {
                fs.Write(buf, 0, buf.Length);
                fs.Flush();
            }
        }

        public void ExportProductionDaily(DataTable dtSource, string strFileName, DataView dvXH = null)
        {
            //创建工作簿 office2007以上
            XSSFWorkbook workbook = new XSSFWorkbook();
            //为工作簿创建工作表并命名
            ISheet sheet = workbook.CreateSheet("日报表");


            //设置列宽
            sheet.SetColumnWidth(0, 15 * 256);
            sheet.SetColumnWidth(1, 10 * 256);
            sheet.SetColumnWidth(2, 10 * 256);
            sheet.SetColumnWidth(3, 10 * 256);
            sheet.SetColumnWidth(4, 10 * 256);
            sheet.SetColumnWidth(5, 10 * 256);
            sheet.SetColumnWidth(7, 12 * 256);



            ICellStyle dateStyle = workbook.CreateCellStyle();

            IDataFormat format = workbook.CreateDataFormat();
            dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");

            string fontName = "黑体";
            ICellStyle totalHeader = workbook.CreateCellStyle();
            //垂直居中对齐
            totalHeader.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
            totalHeader.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
            //边框
            totalHeader.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
            totalHeader.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
            totalHeader.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
            totalHeader.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;

            //字体
            IFont totalFont = workbook.CreateFont();
            totalFont.FontName = fontName;
            totalFont.FontHeightInPoints = 16;
            totalFont.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold;
            totalHeader.SetFont(totalFont);

            IRow row;
            ICell cell;

            //行索引
            int rowIndex = 0;

            //标题
            row = sheet.CreateRow(rowIndex);
            cell = row.CreateCell(0);
            cell.SetCellValue("单丝产量日工资表");

            row.HeightInPoints = 30;
           
            //设置单元格样式
            cell.CellStyle = totalHeader;

            for (int i = 1; i < 8; i++)
            {
                cell = row.CreateCell(i);
            }
            //单元格合并 合并列
            sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(rowIndex, rowIndex, 0, 7));
            rowIndex++;


            row = sheet.CreateRow(rowIndex);
            cell = row.CreateCell(0);
            cell.SetCellValue("20230924");
            for (int i = 1; i < 8; i++)
            {
                cell = row.CreateCell(i);
                cell.CellStyle = dateStyle;
            }
            //单元格合并 合并列
            sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(rowIndex, rowIndex, 0, 7));
            rowIndex++;

            //记录起始项
            int startIndex = rowIndex;





        

            #region 表头及样式
            int cellIndex = 0;
            IRow headerRow = sheet.CreateRow(rowIndex);
            for (int i = 0; i < dtSource.Columns.Count; i++)
            {
                #region MyRegion
                string ColumnsName = dtSource.Columns[i].ToString();
                if (dtSource.Columns[i].ColumnName.EndsWith("BH"))
                {
                    ColumnsName = "员工编号";
                    sheet.SetColumnWidth(cellIndex, 3000);
                    //sheet.SetColumnHidden(cellIndex, true);隐藏指定列
                }
                else if (dtSource.Columns[i].ColumnName.EndsWith("CJH"))
                {
                    ColumnsName = "车间号";
                    sheet.SetColumnWidth(cellIndex, 10000);//设置列宽
                }
                else if (dtSource.Columns[i].ColumnName.EndsWith("Num"))
                {
                    ColumnsName = "数量";
                    sheet.SetColumnWidth(cellIndex, 5000);
                }

                else if (dtSource.Columns[i].ColumnName.EndsWith("Summation"))
                {
                    ColumnsName = "合计（元）";
                    sheet.SetColumnWidth(cellIndex, 5000);
                }
                #endregion

                //设置行高
                headerRow.HeightInPoints = 50;
                headerRow.CreateCell(cellIndex).SetCellValue(ColumnsName);

                ICellStyle headStyle = workbook.CreateCellStyle();
                headStyle.WrapText = true;
                IFont font = workbook.CreateFont();
                //字体大小
                font.FontHeightInPoints = 12;
                font.Boldweight = 360;
                headStyle.SetFont(font);
                headerRow.GetCell(cellIndex).CellStyle = headStyle;
                cellIndex++;
            }
            #endregion

            rowIndex++;

        
            #region 拼接最后一行
            IFont fontLast = workbook.CreateFont();
            fontLast.FontHeightInPoints = 30;
            fontLast.Boldweight = 480;
            IRow dataRowLast = sheet.CreateRow(rowIndex);
            dataRowLast.HeightInPoints = 40;
            ICell newCelLast = dataRowLast.CreateCell(0);
            ICellStyle styleLast = workbook.CreateCellStyle();
            styleLast.DataFormat = format.GetFormat("text");
            styleLast.SetFont(fontLast);
            newCelLast.SetCellValue("制作人：张三");
            sheet.AddMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 0, 3));
            #endregion

            MemoryStream stream = new MemoryStream();
            workbook.Write(stream);
            var buf = stream.ToArray();
            using (FileStream fs = new FileStream(strFileName, FileMode.Create, FileAccess.Write))
            {
                fs.Write(buf, 0, buf.Length);
                fs.Flush();
            }
        }

    }
    }
